package com.example.demo.mapper;

import com.example.demo.entity.DefenseGroupStudent;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.model.DefenseGroupStudentDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 答辩学生表 Mapper 接口
 * </p>
 *
 * @author liwei
 * @since 2024-11-15
 */
@Mapper
public interface DefenseGroupStudentMapper extends BaseMapper<DefenseGroupStudent> {


    @Select("SELECT " +
            "dgs.*,s.student_name,s.student_number,m.major_name AS student_major_name" +
            ",sy.major_id AS student_major_id,s2.second_name AS student_second_name" +
            ",sy.second_id AS student_second_id " +
            "FROM defense_group_student dgs  " +
            "INNER JOIN defense_group dg ON dg.defense_group_id = dgs.defense_group_id  " +
            "LEFT JOIN student s ON   s.student_id = dgs.student_id   AND s.is_delete = 0  " +
            "LEFT JOIN student_year sy ON sy.student_id = dgs.student_id  AND sy.subject_id = dgs.subject_id   AND sy.is_delete = 0  " +
            "LEFT JOIN `second` s2 ON s2.second_id = sy.second_id AND s2.is_delete = 0   " +
            "LEFT JOIN major m ON m.major_id = sy.major_id AND m.is_delete = 0   " +
            "WHERE dgs.is_delete = 0 AND dg.is_delete = 0  AND dgs.defense_group_id=#{defenseGroupId}")
    List<DefenseGroupStudentDto> getDefenseGroupStudentByGroupId(@Param("defenseGroupId")Long defenseGroupId);
}
